/*
 * Copyright (c) 2010 CCX(China) Co.,Ltd. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of
 * CCX(China) Co.,Ltd. ("Confidential Information").
 * It may not be copied or reproduced in any manner without the express 
 * written permission of CCX(China) Co.,Ltd.
 *
 * Author: zhaosj
 * Date: 2010-4-9 下午01:42:25
 */
package com.ccxe.manage.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.ccxe.manage.db.CcxDB;
import com.ccxe.manage.db.DBFactory;
import com.ccxe.manage.model.MenuTree;

/**
 * 页面导航菜单数据处理类
 * 
 * @author zhaosj
 * 
 */
public class MenuItemDao {

	private static final Log logger = LogFactory.getLog(MenuItemDao.class);
	/**
	 * 获取导航菜单信息列表
	 * 
	 * @param styleFlag
	 *            导航树类型
	 * @param userId
	 *            用户ID
	 * @return 导航信息列表
	 * @throws Exception
	 */
	public List<MenuTree> getNavTreeList(int styleFlag, String userId)
			throws Exception {

		String sql = "select tab0.* from ("
				+ " select level,t.menu_id, t.parent_id, t.menu_nm, t.menu_url,t.is_leaf,t.style_flag,t.order_seq"
				+ " from menu_item_tmp t start with parent_id = 0"
				+ " connect by prior menu_id = parent_id and style_flag = ?) tab0 "
				+ " where tab0.menu_id in( select m.menu_id from C_USER u left join C_USER_ROLE r on u.user_id = r.user_id "
				+ " left join C_MENU_ROLE m on m.role_id = r.role_id where u.user_id = ?)"
				+ " order by tab0.order_seq ";

		CcxDB db = null;
		ResultSet rs = null;
		List<MenuTree> list = new ArrayList<MenuTree>();

		try {
			db = DBFactory.getInstance();
			Object[] obj = new Object[2];
			obj[0] = styleFlag;
			obj[1] = userId;

			rs = db.executeQuery(sql, obj);
			String pid = "0";
			String url = "";
			while (rs.next()) {
				pid = rs.getString("parent_id");
				if (pid == null || "null".equals(pid) || "".equals(pid.trim()))
					pid = "0";
				url = rs.getString("menu_url");
				if (url == null)
					url = "";

				MenuTree mt = new MenuTree();
				mt.setMenu_id(rs.getString("menu_id"));
				mt.setMenu_nm(rs.getString("menu_nm"));
				mt.setMenu_url(url);
				mt.setIs_leaf(rs.getInt("is_leaf"));
				mt.setLevel(rs.getInt("level"));
				mt.setParent_id(pid);
				mt.setStyle_flag(rs.getInt("style_flag"));

				list.add(mt);
			}

			rs.close();
			db.close();

		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			if (rs != null)
				rs.close();
			if (db != null)
				db.close();
		}

		return list;
	}

	/**
	 * 获取二级导航菜单信息列表
	 * 
	 * @param styleFlag
	 *            导航树类型
	 * @param userId
	 *            用户ID
	 * @return 导航信息列表
	 * @throws Exception
	 */
	public List<MenuTree> getMenuListByPid(int styleFlag, String parentId,String userId)
			throws Exception {

		String sql = " select t.menu_id, t.parent_id, t.menu_nm, t.menu_url,t.is_leaf,t.style_flag,t.order_seq"
				+ " from menu_item_tmp t where t.style_flag = ? and t.parent_id = ?"
				+ " and t.menu_id in( select m.menu_id from C_USER u left join C_USER_ROLE r on u.user_id = r.user_id "
				+ " left join C_MENU_ROLE m on m.role_id = r.role_id where u.user_id = ?)"
				+ " order by t.order_seq";

		CcxDB db = null;
		ResultSet rs = null;
		List<MenuTree> list = new ArrayList<MenuTree>();

		try {
			db = DBFactory.getInstance();
			Object[] obj = new Object[3];
			obj[0] = styleFlag;
			obj[1] = parentId;
			obj[2] = userId;
			rs = db.executeQuery(sql, obj);
			String pid = "0";
			String url = "";
			while (rs.next()) {
				pid = rs.getString("parent_id");
				if (pid == null || "null".equals(pid) || "".equals(pid.trim()))
					pid = "0";
				url = rs.getString("menu_url");
				if (url == null)
					url = "";

				MenuTree mt = new MenuTree();
				mt.setMenu_id(rs.getString("menu_id"));
				mt.setMenu_nm(rs.getString("menu_nm"));
				mt.setMenu_url(url);
				mt.setIs_leaf(rs.getInt("is_leaf"));
				mt.setParent_id(pid);
				mt.setStyle_flag(rs.getInt("style_flag"));

				list.add(mt);
			}

			rs.close();
			db.close();

		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			if (rs != null)
				rs.close();
			if (db != null)
				db.close();
		}

		return list;
	}

	/**根据menuid得到menutree对象
	 * @author haofan
	 * @param menuId
	 * @return
	 * @throws Exception
	 */
	public MenuTree getMenuById(String menuId) throws Exception {
		String sql = " select t.menu_id, t.parent_id, t.menu_nm, t.menu_url,t.is_leaf,t.style_flag"
				+ " from menu_item_tmp t where t.menu_id = ? ";
		CcxDB db = null;
		ResultSet rs = null;
		MenuTree mt = new MenuTree();
		try {
			db = DBFactory.getInstance();
			Object[] obj = new Object[1];
			obj[0] = menuId;
			rs = db.executeQuery(sql, obj);
			String pid = "0";
			String url = "";
			while (rs.next()) {
				pid = rs.getString("parent_id");
				if (pid == null || "null".equals(pid) || "".equals(pid.trim()))
					pid = "0";
				url = rs.getString("menu_url");
				if (url == null)
					url = "";

				mt.setMenu_id(rs.getString("menu_id"));
				mt.setMenu_nm(rs.getString("menu_nm"));
				mt.setMenu_url(url);
				mt.setIs_leaf(rs.getInt("is_leaf"));
				mt.setParent_id(pid);
				mt.setStyle_flag(rs.getInt("style_flag"));

			}

			rs.close();
			db.close();

		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			if (rs != null)
				rs.close();
			if (db != null)
				db.close();
		}
		return mt;
	}
	
	/**
	 * @author haofan
	 * @param menuId
	 * @return
	 * @throws Exception
	 */
	public String findFirstChildren(String menuId) throws Exception {
		String sql = " select t.menu_id "
				+ " from menu_item_tmp t where t.parent_id = ? order by t.menu_id";
		CcxDB db = null;
		ResultSet rs = null;
		try {
			db = DBFactory.getInstance();
			Object[] obj = new Object[1];
			obj[0] = menuId;
			rs = db.executeQuery(sql, obj);
			if (null != rs && rs.next()) {
				return rs.getString("menu_id");
			}
			rs.close();
			db.close();
		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			if (rs != null)
				rs.close();
			if (db != null)
				db.close();
		}
		return null;
	}
	
	/**
	 * @author haofan
	 * @throws Exception 
	 * @date 2010-7-14
	 * 功能：查询moduleId下的所有子节点
	 * 递归查询
	 */
	public List<MenuTree> findMenuItemByMenuId(String pmenuId) throws Exception {

		String sql = " select level ,t.menu_id, t.parent_id, t.menu_nm, t.menu_url,t.is_leaf,t.style_flag  " +
					 " from menu_item_tmp t START WITH  menu_id = ? connect by prior menu_id = parent_id order by menu_id";

		CcxDB db = null;
		ResultSet rs = null;
		List<MenuTree> list = new ArrayList<MenuTree>();

		try {
			db = DBFactory.getInstance();
			Object[] obj = new Object[1];
			obj[0] = pmenuId;
			rs = db.executeQuery(sql, obj);
			String pid = "0";
			String url = "";
			while (rs.next()) {
				pid = rs.getString("parent_id");
				if (pid == null || "null".equals(pid) || "".equals(pid.trim()))
					pid = "0";
				url = rs.getString("menu_url");
				if (url == null)
					url = "";

				MenuTree mt = new MenuTree();
				mt.setMenu_id(rs.getString("menu_id"));
				mt.setMenu_nm(rs.getString("menu_nm"));
				mt.setMenu_url(url);
				mt.setIs_leaf(rs.getInt("is_leaf"));
				mt.setParent_id(pid);
				mt.setStyle_flag(rs.getInt("style_flag"));

				list.add(mt);
			}

			rs.close();
			db.close();

		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			if (rs != null)
				rs.close();
			if (db != null)
				db.close();
		}
		return list;
	}
	
	/**
	 * @author haofan
	 * @throws Exception 
	 * @date 2010-7-14
	 * 功能：查询moduleId下的所有子节点
	 * 递归查询
	 */
	public int canUseMenu(String userId,String url) throws Exception {

		String sql = " select count(*) from menu_item_tmp t  " 
					+ " where t.menu_id in( select m.menu_id from C_USER u left join C_USER_ROLE r on u.user_id = r.user_id "
					+ " left join C_MENU_ROLE m on m.role_id = r.role_id where u.user_id = '"+userId+"')"
					+ " and  t.menu_url ='"+url+"'";
		logger.info("SQL:"+sql);
		CcxDB db = null;
		int rs = 0;
		try {
			db = DBFactory.getInstance();
			rs = db.getSearchTotalRowSize(sql);
			db.close();
		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			if (db != null)
				db.close();
		}

		return rs;
	}
}
